const fs = require('fs').promises;
const path = require('path');
const mysql = require('mysql2/promise');

// 数据库配置
require('dotenv').config({ path: '../.env' });

const dbConfig = {
  host: process.env.DB_HOST || 'localhost',
  port: process.env.DB_PORT || 3306,
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '',
  database: process.env.DB_NAME || 'cu_mr_bull_prod',
  charset: 'utf8mb4',
  timezone: '+08:00'
};

// 菜品分类映射
const categoryMapping = {
  'promotion': { name: '促销菜品', name_en: 'Promotion', sort_order: 1 },
  'recommended': { name: '推荐菜品', name_en: 'Recommended', sort_order: 2 },
  'combo': { name: '套餐', name_en: 'Combo', sort_order: 3 },
  'main': { name: '主菜', name_en: 'Main Dishes', sort_order: 4 },
  'side': { name: '配菜', name_en: 'Side Dishes', sort_order: 5 },
  'drinks': { name: '饮品', name_en: 'Drinks', sort_order: 6 },
  'dessert': { name: '甜品', name_en: 'Desserts', sort_order: 7 }
};

// 从前端MenuConfig提取的菜品数据
const menuItems = [
  {
    name: "韩式炸鸡套餐",
    englishName: "Korean Fried Chicken Set",
    description: "酥脆外皮，嫩滑内里",
    englishDescription: "Crispy outside, tender inside",
    price: 59.00,
    category: "promotion",
    imageName: "Korean_Fried_Chicken_Set",
    isAvailable: true,
    isSpicy: false,
    isVegetarian: false
  },
  {
    name: "Triple Delight Desert",
    englishName: "Triple Delight Desert",
    description: "豆浆、巧克力和草莓",
    englishDescription: "Soya Milk, Chocolate & Strawberry",
    price: 8.80,
    category: "promotion",
    imageName: "MainPIceC",
    isAvailable: true,
    isSpicy: false,
    isVegetarian: true
  },
  {
    name: "清爽Highball鸡尾酒",
    englishName: "refreshing Highball cocktails",
    description: "完美下午组合",
    englishDescription: "Perfect afternoon combo",
    price: 5.00,
    category: "promotion",
    imageName: "HighBallMpage",
    isAvailable: true,
    isSpicy: false,
    isVegetarian: false,
    drinkType: "alcoholic"
  },
  {
    name: "炸鸡翅-蒜香酱油",
    englishName: "Fried Chicken Wing",
    description: "酥脆多汁的韩式炸鸡翅，外酥内嫩，蘸上特制蒜蓉酱汁，回味无穷",
    englishDescription: "Crispy wings coated in our special garlic soy glaze – savory, sweet, and irresistibly juicy. Golden crispy chicken wings, coated in our signature garlic sauce — finger-licking good.",
    price: 10.00,
    category: "recommended",
    imageName: "Fried_Chicken_Garlic_Soy",
    isAvailable: true,
    isSpicy: false,
    isVegetarian: false,
    ingredientDescription: "鸡翅膀，独家秘制酱料，大蒜",
    englishIngredientDescription: "Chicken wings, house garlic sauce, garlic."
  },
  {
    name: "炸鸡翅-芝士粉",
    englishName: "Fried Chicken Wing - Cheese Powder",
    description: "酥脆多汁的韩式炸鸡翅，外酥内嫩，撒上香浓芝士粉，奶香浓郁",
    englishDescription: "Cheese-coated chicken bites, crispy outside, tender inside — dangerously addictive.",
    price: 10.00,
    category: "recommended",
    imageName: "Fried_Chicken_CheesePowder",
    isAvailable: true,
    isSpicy: false,
    isVegetarian: false,
    ingredientDescription: "鸡翅膀，芝士粉",
    englishIngredientDescription: "Chicken wings, cheese powder"
  },
  {
    name: "经典拌饭",
    englishName: "Classic Bibimbap",
    description: "韩国招牌拌饭，营养均衡的蔬菜搭配牛肉或鸡肉，佐以溏心蛋和独家秘制辣酱。",
    englishDescription: "Korean Signature Bibimbap — a balanced and nutritious mix of vegetables with beef or chicken, topped with a soft-boiled egg and our exclusive house-made spicy sauce.",
    price: 12.50,
    category: "recommended",
    imageName: "Classic_Bibimbap",
    isAvailable: true,
    isSpicy: false,
    isVegetarian: false,
    ingredientDescription: "白米饭，牛肉或鸡肉，菠菜，胡萝卜，香菇，黄瓜，豆芽菜，煎蛋，秘制辣椒酱，泡菜",
    englishIngredientDescription: "White rice, beef or chicken, spinach, carrot, shiitake mushroom, cucumber, bean sprouts, fried egg, Secret recipe spicy sauce, kimchi."
  },
  {
    name: "雪浓汤",
    englishName: "Seolleongtang",
    description: "慢火熬煮8小时的牛骨浓汤，乳白醇厚，不添加任何添加剂，滋补又健康。",
    englishDescription: "Slow-simmered for 8 hours, our beef bone broth is rich and milky-white, with no added additives — nourishing and healthy.",
    price: 12.50,
    category: "recommended",
    imageName: "SeolleongTang",
    isAvailable: true,
    isSpicy: false,
    isVegetarian: false,
    ingredientDescription: "牛骨汤、牛腿肉片、葱片、面条、韩式小菜",
    englishIngredientDescription: "Beef bone soup, sliced beef leg, scallions, noodles, Korean side dishes"
  },
  {
    name: "烧牛助骨",
    englishName: "Galbi Jjim (Braised Beef Ribs)",
    description: "精选牛肋骨慢火炖煮，酱香浓郁，肉质入味，是一道不可错过的韩式经典。",
    englishDescription: "Slow-braised beef short ribs, richly marinated with deep umami — a classic Korean dish not to be missed.",
    price: 28.50,
    category: "recommended",
    imageName: "galbi_jjim",
    isAvailable: true,
    isSpicy: false,
    isVegetarian: false,
    ingredientDescription: "牛助骨、杏鲍菇、青辣椒、红辣椒、米饭、小菜、泡饭",
    englishIngredientDescription: "Beef short ribs, king oyster mushrooms, green chili, red chili, rice, side dishes, rice soup"
  },
  {
    name: "双人套餐",
    englishName: "Couple Set",
    description: "两份主菜 + 两杯饮料，限时优惠",
    englishDescription: "Two main dishes + two drinks, limited time offer",
    price: 29.90,
    category: "combo",
    imageName: "couple_set",
    isAvailable: true,
    isSpicy: false,
    isVegetarian: false
  },
  {
    name: "经典套餐A",
    englishName: "Classic Set A",
    description: "主菜 + 配菜 + 饮料",
    englishDescription: "Main dish + side dish + drink",
    price: 22.90,
    category: "combo",
    imageName: "classic_set_a",
    isAvailable: true,
    isSpicy: false,
    isVegetarian: false
  },
  {
    name: "凉拌冷面",
    englishName: "Bibim Naengmyeon",
    description: "清爽Q弹的荞麦冷面，搭配独家秘制辣酱，是炎热天气的绝佳选择。",
    englishDescription: "Refreshing and chewy buckwheat cold noodles, served with our exclusive house spicy sauce — a perfect choice for hot weather.",
    price: 12.50,
    category: "main",
    imageName: "Bibim_Naengmyeon",
    isAvailable: true,
    isSpicy: true,
    isVegetarian: false,
    ingredientDescription: "荞麦冷面、牛肉或鸡肉、腌萝卜、白泡菜、黄瓜丝、煮鸡蛋、独家秘制辣酱、小菜、泡菜",
    englishIngredientDescription: "Buckwheat cold noodles, beef or chicken, pickled radish, white kimchi, shredded cucumber, boiled egg, Secret recipe spicy sauce, side dishes, kimchi"
  },
  {
    name: "麻辣年糕",
    englishName: "Mala Tteokbokki",
    description: "年糕炒制于独家秘制麻辣酱中，香辣过瘾。",
    englishDescription: "Rice cakes stir-fried in house-made mala (numbing spicy) sauce — bold and satisfying.",
    price: 13.50,
    category: "main",
    imageName: "Mala_Tteokbokki",
    isAvailable: true,
    isSpicy: true,
    isVegetarian: false,
    ingredientDescription: "年糕、鱼饼、宽粉、芝士、秘制麻辣酱",
    englishIngredientDescription: "Rice cakes, fish cakes, wide glass noodles, cheese, house special mala sauce"
  },
  {
    name: "泡菜拉面",
    englishName: "Kimchi Ramen",
    description: "泡菜融合劲道拉面，辣中带鲜，搭配煎蛋与牛/鸡肉，暖胃又过瘾。",
    englishDescription: "Spicy kimchi ramen, with a kick of freshness, paired with a fried egg and your choice of beef or chicken — warming and delicious.",
    price: 12.50,
    category: "main",
    imageName: "Kimchi_Ramen",
    isAvailable: true,
    isSpicy: true,
    isVegetarian: false,
    ingredientDescription: "拉面、泡菜、香菇、牛肉或鸡肉、煎蛋、葱片、小菜",
    englishIngredientDescription: "Ramen, kimchi, shiitake mushrooms, beef or chicken, fried egg, scallions, side dishes"
  },
  {
    name: "泡菜炒饭",
    englishName: "Kimchi Fried Rice",
    description: "酸辣泡菜与香喷喷米饭一同炒制，锅气十足，带来辛香口感。",
    englishDescription: "Stir-fried kimchi and fragrant rice, full of wok hei (wok fragrance), delivering a spicy, savory sensation.",
    price: 12.50,
    category: "main",
    imageName: "Kimchi_Fried_Rice",
    isAvailable: true,
    isSpicy: true,
    isVegetarian: false,
    ingredientDescription: "米饭、泡菜、鸡肉或牛肉、煎蛋、酱油、香油、海苔、小菜",
    englishIngredientDescription: "Rice, kimchi, chicken or beef, fried egg, soy sauce, sesame oil, seaweed, side dishes"
  },
  {
    name: "辣炖鸡腿",
    englishName: "Dakbokkeumtang",
    description: "鸡腿肉炖煮入味，搭配土豆胡萝卜，浓郁辣酱汤底，香辣暖胃。",
    englishDescription: "Braised chicken leg with potato and carrot, simmered in a rich spicy broth — warming and flavorful.",
    price: 12.50,
    category: "main",
    imageName: "Dakbokkeumtang",
    isAvailable: true,
    isSpicy: true,
    isVegetarian: false,
    ingredientDescription: "鸡腿肉、土豆、胡萝卜、香菇、洋葱、米饭、小菜、泡菜",
    englishIngredientDescription: "Chicken leg, potato, carrot, shiitake mushroom, onion, rice, side dishes, kimchi"
  },
  {
    name: "紫菜饭团",
    englishName: "Korean Seaweed Rice Ball",
    description: "手工饭团混合香油与紫菜，外脆内香，是绝佳小吃或配菜。",
    englishDescription: "Handmade rice balls mixed with sesame oil and seaweed — crispy outside, soft inside — a great snack or side dish.",
    price: 8.00,
    category: "side",
    imageName: "Korean_Seaweed_Rice_Ball",
    isAvailable: true,
    isSpicy: false,
    isVegetarian: true,
    ingredientDescription: "米饭、紫菜、香油、泡菜",
    englishIngredientDescription: "Rice, seaweed, sesame oil, kimchi"
  },
  {
    name: "糖醋鸡块",
    englishName: "Chicken Tangsuyuk",
    description: "韩式酥炸鸡块，外酥里嫩，淋上酸甜开胃的秘制糖醋汁，搭配黄瓜与胡萝卜，色香味俱全。",
    englishDescription: "Korean fried chicken bites, crispy on the outside, juicy on the inside, drizzled with our sweet and tangy house sauce, served with cucumber and carrot — vibrant and flavorful.",
    price: 11.90,
    category: "side",
    imageName: "chicken_tangsuyuk",
    isAvailable: true,
    isSpicy: false,
    isVegetarian: false,
    ingredientDescription: "鸡腿肉、黄瓜、洋葱、胡萝卜、秘制糖醋汁",
    englishIngredientDescription: "Chicken leg, cucumber, onion, carrot, Secret recipe sweet and sour sauce"
  },
  {
    name: "切片牛腩肉",
    englishName: "Sliced Beef Brisket",
    description: "慢煮牛腩切片，搭配葱花，肉质软嫩，是营养加餐的绝佳选择。",
    englishDescription: "Sliced slow-cooked beef brisket with chopped scallions — tender and perfect for a nutritious side.",
    price: 14.90,
    category: "side",
    imageName: "sliced_beef_brisket",
    isAvailable: true,
    isSpicy: false,
    isVegetarian: false,
    ingredientDescription: "牛腹肉、葱、蘸料",
    englishIngredientDescription: "Beef brisket, scallions, dipping sauce"
  },
  {
    name: "炸饺子",
    englishName: "Fried Mandu",
    description: "外酥内鲜的韩式炸饺，饱满多汁，搭配特调蘸酱，香气扑鼻。",
    englishDescription: "Crispy and juicy Korean dumplings, packed with savory filling and paired with a special dipping sauce — irresistibly aromatic.",
    price: 10.00,
    category: "side",
    imageName: "fried_mandu",
    isAvailable: true,
    isSpicy: false,
    isVegetarian: false
  },
  {
    name: "Highball威士忌",
    englishName: "Highball Whiskey",
    description: "经典威士忌调酒",
    englishDescription: "Classic whiskey cocktail",
    price: 10.00,
    category: "drinks",
    imageName: "highball",
    isAvailable: true,
    isSpicy: false,
    isVegetarian: false,
    drinkType: "alcoholic"
  },
  {
    name: "Terra纯麦啤酒",
    englishName: "Terra Pure Malt Beer",
    description: "韩国进口纯麦啤酒",
    englishDescription: "Korean imported pure malt beer",
    price: 9.90,
    category: "drinks",
    imageName: "terra_beer",
    isAvailable: true,
    isSpicy: false,
    isVegetarian: false,
    drinkType: "alcoholic"
  },
  {
    name: "美式咖啡",
    englishName: "Americano",
    description: "经典美式咖啡",
    englishDescription: "Classic American coffee",
    price: 2.00,
    category: "drinks",
    imageName: "americano",
    isAvailable: true,
    isSpicy: false,
    isVegetarian: true,
    drinkType: "nonAlcoholic"
  },
  {
    name: "卡布奇诺",
    englishName: "Cappuccino",
    description: "意式卡布奇诺咖啡",
    englishDescription: "Italian cappuccino coffee",
    price: 3.80,
    category: "drinks",
    imageName: "cappuccino",
    isAvailable: true,
    isSpicy: false,
    isVegetarian: true,
    drinkType: "nonAlcoholic"
  },
  {
    name: "拿铁咖啡",
    englishName: "Caffe Latte",
    description: "香浓拿铁咖啡",
    englishDescription: "Rich latte coffee",
    price: 3.80,
    category: "drinks",
    imageName: "latte",
    isAvailable: true,
    isSpicy: false,
    isVegetarian: true,
    drinkType: "nonAlcoholic"
  },
  {
    name: "冰梅汁",
    englishName: "Ice Plum Juice",
    description: "清爽冰梅汁",
    englishDescription: "Refreshing iced plum juice",
    price: 4.00,
    category: "drinks",
    imageName: "plum_juice",
    isAvailable: true,
    isSpicy: false,
    isVegetarian: true,
    drinkType: "nonAlcoholic"
  },
  {
    name: "软饮料",
    englishName: "Soft Drinks",
    description: "各种软饮料",
    englishDescription: "Various soft drinks",
    price: 2.50,
    category: "drinks",
    imageName: "soft_drinks",
    isAvailable: true,
    isSpicy: false,
    isVegetarian: true,
    drinkType: "nonAlcoholic"
  },
  {
    name: "脆梨汁",
    englishName: "Crisp Pear Juice",
    description: "新鲜脆梨汁",
    englishDescription: "Fresh crisp pear juice",
    price: 3.50,
    category: "drinks",
    imageName: "pear_juice",
    isAvailable: true,
    isSpicy: false,
    isVegetarian: true,
    drinkType: "nonAlcoholic"
  },
  {
    name: "乐天牛奶汽水",
    englishName: "Lotte Milkis",
    description: "韩国乐天牛奶汽水",
    englishDescription: "Korean Lotte milk soda",
    price: 2.50,
    category: "drinks",
    imageName: "milkis",
    isAvailable: true,
    isSpicy: false,
    isVegetarian: true,
    drinkType: "nonAlcoholic"
  },
  {
    name: "冰柚子柠檬茶",
    englishName: "Ice Yuzu Lemon Tea",
    description: "清爽冰柚子柠檬茶",
    englishDescription: "Refreshing iced yuzu lemon tea",
    price: 4.00,
    category: "drinks",
    imageName: "ice_yuzu_lemon_tea",
    isAvailable: true,
    isSpicy: false,
    isVegetarian: true,
    drinkType: "nonAlcoholic"
  }
];

class MenuDataSyncer {
  constructor() {
    this.connection = null;
  }

  async connect() {
    try {
      this.connection = await mysql.createConnection(dbConfig);
      console.log('✅ 数据库连接成功');
    } catch (error) {
      console.error('❌ 数据库连接失败:', error.message);
      throw error;
    }
  }

  async disconnect() {
    if (this.connection) {
      await this.connection.end();
      console.log('✅ 数据库连接已关闭');
    }
  }

  async syncCategories() {
    console.log('\n📂 开始同步菜品分类...');
    
    try {
      for (const [key, category] of Object.entries(categoryMapping)) {
        const [existing] = await this.connection.execute(
          'SELECT id FROM menu_categories WHERE name_en = ?',
          [category.name_en]
        );

        if (existing.length === 0) {
          await this.connection.execute(
            `INSERT INTO menu_categories (name, name_en, description, description_en, sort_order, is_active, created_at, updated_at) 
             VALUES (?, ?, ?, ?, ?, ?, NOW(), NOW())`,
            [
              category.name,
              category.name_en,
              `${category.name}分类`,
              `${category.name_en} category`,
              category.sort_order,
              true
            ]
          );
          console.log(`✅ 创建分类: ${category.name} / ${category.name_en}`);
        } else {
          console.log(`⏭️  分类已存在: ${category.name} / ${category.name_en}`);
        }
      }
    } catch (error) {
      console.error('❌ 同步分类失败:', error.message);
      throw error;
    }
  }

  async getCategoryId(categoryKey) {
    const category = categoryMapping[categoryKey];
    if (!category) {
      throw new Error(`未找到分类: ${categoryKey}`);
    }

    const [rows] = await this.connection.execute(
      'SELECT id FROM menu_categories WHERE name_en = ?',
      [category.name_en]
    );

    if (rows.length === 0) {
      throw new Error(`数据库中未找到分类: ${category.name_en}`);
    }

    return rows[0].id;
  }

  async syncMenuItems() {
    console.log('\n🍽️  开始同步菜品数据...');
    
    let successCount = 0;
    let skipCount = 0;
    let errorCount = 0;

    for (const item of menuItems) {
      try {
        // 检查菜品是否已存在
        const [existing] = await this.connection.execute(
          'SELECT id FROM menu_items WHERE name = ? AND name_en = ?',
          [item.name, item.englishName]
        );

        if (existing.length > 0) {
          console.log(`⏭️  菜品已存在: ${item.name} / ${item.englishName}`);
          skipCount++;
          continue;
        }

        // 获取分类ID
        const categoryId = await this.getCategoryId(item.category);

        // 插入菜品数据
        await this.connection.execute(
          `INSERT INTO menu_items (
            name, name_en, description, description_en, price, category_id, 
            image_url, is_available, is_featured, preparation_time, 
            spice_level, ingredients, tags, sort_order, created_at, updated_at
          ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())`,
          [
            item.name,
            item.englishName,
            item.description,
            item.englishDescription,
            item.price,
            categoryId,
            item.imageName ? `/images/menu/${item.imageName}.jpg` : null,
            item.isAvailable !== false,
            false, // is_featured 默认为false
            null, // preparation_time
            item.isSpicy ? 2 : 0, // spice_level: 0=不辣, 1=微辣, 2=中辣, 3=很辣
            item.ingredientDescription || null,
            JSON.stringify({
              isVegetarian: item.isVegetarian || false,
              drinkType: item.drinkType || null,
              englishIngredients: item.englishIngredientDescription || null
            }),
            0 // sort_order
          ]
        );

        console.log(`✅ 创建菜品: ${item.name} / ${item.englishName} (¥${item.price})`);
        successCount++;
      } catch (error) {
        console.error(`❌ 同步菜品失败 [${item.name}]:`, error.message);
        errorCount++;
      }
    }

    console.log(`\n📊 菜品同步完成:`);
    console.log(`   ✅ 成功: ${successCount} 个`);
    console.log(`   ⏭️  跳过: ${skipCount} 个`);
    console.log(`   ❌ 失败: ${errorCount} 个`);
  }

  async run() {
    try {
      console.log('🚀 开始同步前端APP菜品数据到后台数据库...');
      
      await this.connect();
      await this.syncCategories();
      await this.syncMenuItems();
      
      console.log('\n🎉 菜品数据同步完成!');
    } catch (error) {
      console.error('\n💥 同步过程中发生错误:', error.message);
      process.exit(1);
    } finally {
      await this.disconnect();
    }
  }
}

// 运行同步脚本
if (require.main === module) {
  const syncer = new MenuDataSyncer();
  syncer.run().catch(console.error);
}

module.exports = MenuDataSyncer;